/**   
 * @Title: ExcelWriter.java 
 * @Package com.bill99.report.util 
 * @Description: Excel处理类 
 * @author juntao.gao@99bill.com   
 * @date 2016-6-20 下午03:24:16 
 * @version V1.0
 */
package com.bill99.util.excel;

import com.bill99.util.DateUtil;
import com.bill99.util.StringFormat;
import com.bill99.util.StringUtil;
import org.apache.commons.io.IOUtils;
import org.apache.commons.jxpath.JXPathContext;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;

import java.io.*;
import java.math.BigDecimal;
import java.util.*;
import java.util.Map.Entry;

/**
 * @author juntao.gao
 *
 */
public class ExcelWriter {

    private static final Logger logger = LoggerFactory.getLogger(ExcelWriter.class);

    /**
     * 默认sheet最大尺寸65000
     */
    private static final int MAX_PER_SHEET = 65000;

    /**
     * vxls模版文件名(需唯一 src/main/resource/template文件夹下)
     */
    private String vxlsFile;

    /**
     * 表格容器
     */
    private ExcelSheet excelSheet;

    /**
     * excel的版本2003 或 2007(默认)
     */
    private String xlsVersion = "2007";

    /**
     * excel工作簿
     */
    private Workbook workbook;

    /**
     * excel容器sheet
     */
    private Sheet hSheet;

    /**
     * sheet数量
     */
    private int sheetNum = 0;

    /**
     * 记录计数器
     */
    private int cnt = 0;

    /**
     * 类型转换器
     */
    @SuppressWarnings("rawtypes")
    private ObjectConvertor convertor;

    /**
     * 表头样式
     */
    private CellStyle styleTitle;

    /**
     * 表体样式
     */
    private CellStyle styleBorder;

    /**
     * 记录需计算的列序号-计算值
     */
    private final Map<Integer, Double> sumCols = new HashMap<Integer, Double>();

    /**
     * 模版资源
     */
    private Map<String, ClassPathResource> templateResourceMap;

    /**
     * 默认构造对象
     */
    public ExcelWriter() {
        super();
    }

    /**
     * 构造指定模版和数据转换器的对象
     * @param vxlsFile
     * @param convertor
     */
    public ExcelWriter(String vxlsFile, ObjectConvertor<?, ?> convertor) {
        super();
        this.vxlsFile = vxlsFile;
        this.convertor = convertor;
    }

    /**
     * 构造指定模版,Excel版本,数据转换器的对象
     * @param vxlsFile
     * @param xlsVersion
     * @param convertor
     */
    public ExcelWriter(String vxlsFile, String xlsVersion, ObjectConvertor<?, ?> convertor) {
        super();
        this.vxlsFile = vxlsFile;
        this.xlsVersion = xlsVersion;
        this.convertor = convertor;
    }

    /**
     * 
     * @Title: init
     * @Description: 文件初始化
     * @return void 返回类型
     * @throws
     */
    private void init() {

        if ("2003".equals(xlsVersion)) {
            workbook = new HSSFWorkbook();
        } else if ("2007".equals(xlsVersion)) {
            workbook = new SXSSFWorkbook(1000); // 修改为缓存模式
        }

        excelSheet = loadSheet(vxlsFile); // 导入excel模版设置(包含文件标题，字段标题，数据格式等)
        if (hSheet == null) { // 创建sheet
            hSheet = workbook.createSheet("sheet" + (sheetNum++));
        }
        // 设置表头样式
        styleTitle = workbook.createCellStyle();
        styleTitle.setAlignment(CellStyle.ALIGN_CENTER);
        styleTitle.setBorderBottom(CellStyle.BORDER_THIN);
        styleTitle.setBorderLeft(CellStyle.BORDER_THIN);
        styleTitle.setBorderRight(CellStyle.BORDER_THIN);
        styleTitle.setBorderTop(CellStyle.BORDER_THIN);
        // 设置表体样式
        styleBorder = workbook.createCellStyle();
        styleBorder.setBorderBottom(CellStyle.BORDER_THIN);
        styleBorder.setBorderLeft(CellStyle.BORDER_THIN);
        styleBorder.setBorderRight(CellStyle.BORDER_THIN);
        styleBorder.setBorderTop(CellStyle.BORDER_THIN);
        //add lihua.jiang 20160715
        styleBorder.setAlignment(CellStyle.ALIGN_RIGHT);

    }

    /**
     * 加载表格定义对象
     * 
     * @param sheetResId
     * @return
     */
    private ExcelSheet loadSheet(String templateName) {
        String templatePath = "template/" + templateName;
        InputStream inputStream = null;
        SAXReader reader = new SAXReader();
        ExcelSheet sheet = new ExcelSheet();
        try {
            inputStream = findTemplateResource(templatePath).getInputStream();
            Document document = reader.read(inputStream);

            // 获取根节点
            Element root = document.getRootElement();
            sheet.setBeanClass(root.attributeValue("beanClass"));
            //excel头背景色
            sheet.setHeadBackgroundColor(root.attributeValue("head_background_color"));
            //字体加粗
            sheet.setFontWeight(root.attributeValue("font_weight"));
            //字体大小
            sheet.setFontSize(root.attributeValue("font_size"));
            sheet.setTitle(StringUtil.parseToString(root.attributeValue("title")));
            // 当前节点下面子节点迭代器
            @SuppressWarnings("unchecked")
            Iterator<Element> it = root.elementIterator();
            // 遍历
            while (it.hasNext()) {
                // 获取某个子节点对象
                Element e = it.next();
                ExcelColumn column = new ExcelColumn();
                column.setProperty(e.attributeValue("property"));
                column.setSummary(StringUtil.asBool(e.attributeValue("summary"), false));
                column.setTitle(e.attributeValue("title"));
                column.setDisplayPattern(e.attributeValue("displayPattern"));
                column.setDataType(e.attributeValue("dataType"));
                // 居中居右居左
                column.setAlign(e.attributeValue("align"));
                // 类合并
                column.setClospan(e.attributeValue("clospan"));
                // 行合并
                column.setRowspan(e.attributeValue("rowspan"));
                sheet.addColumn(column);
            }

        } catch (DocumentException e) {
            logger.error("template file parese error", e);
        } catch (IOException e) {
            logger.error("template file read error", e);
        } finally {
            IOUtils.closeQuietly(inputStream);
        }

        return sheet;
    }

    /**
     * 
     * @Title: findTemplateResource
     * @Description: 获取模版文件
     * @param @param templatePath
     * @param @return 设定文件
     * @return ClassPathResource 返回类型
     * @throws
     */
    private ClassPathResource findTemplateResource(String templatePath) {
        if (templateResourceMap == null) {
            templateResourceMap = new HashMap<String, ClassPathResource>();
        }
        ClassPathResource templateResource = templateResourceMap.get(templatePath);
        if (templateResource == null) {
            templateResource = new ClassPathResource(templatePath);
            templateResourceMap.put(templatePath, templateResource);
        }
        return templateResource;
    }

    /**
     * 
     * @Title: generateTitle
     * @Description: 生成文件标题,列表标题及sheet名称
     * @return void 返回类型
     * @throws
     */
    private void generateTitle() {
        boolean isMerge = false;
        int colNum = 0;
        Row rowTitle = hSheet.createRow(0);
        if (excelSheet.getTitle() != null) {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, excelSheet.getColumns().size() - 1);
            hSheet.addMergedRegion(cellRangeAddress);
            Cell cell = rowTitle.createCell((short) 0);
            cell.setCellValue(excelSheet.getTitle());
            cell.setCellStyle(styleTitle);
            rowTitle = hSheet.createRow(hSheet.getLastRowNum() + 1); // 产生Excel报表标题，新增一行
        }

        int tmp = 0;
        for (ExcelColumn col : excelSheet.getColumns()) {
            if (col.getClospan() == null && col.getRowspan() == null) {
                if (isMerge) {
                    isMerge = false;
                    tmp = 0;
                    rowTitle = hSheet.createRow(hSheet.getLastRowNum() + 1); // 产生列表标题 此处修改
                }
                tmp = createCell(col, rowTitle, tmp); //此处修改
            }
            //列合并
            if (col.getClospan() != null) {
                int colResult = Integer.parseInt(col.getClospan());
                for (int i = 0; i < colResult; i++) {
                    tmp = createCell(col, rowTitle, tmp);
                }
                CellRangeAddress head = new CellRangeAddress(0, 0, colNum, tmp - 1);
                hSheet.addMergedRegion(head);
                colNum += colResult;
                isMerge = true;
            }

        }

    }

    /**
     * 创建列
     * @param col
     * @param rowTitle
     * @param tmp
     * @return
     */
    private int createCell(ExcelColumn col, Row rowTitle, int tmp) {
        int cellNum = tmp;
        Cell cell = rowTitle.createCell(cellNum++);
        if (null != excelSheet.getFontSize() || null != excelSheet.getFontWeight()) {
            Font font2 = workbook.createFont();
            //设置字体大小
            if (null != excelSheet.getFontSize()) {
                font2.setFontHeightInPoints((short) Integer.parseInt(excelSheet.getFontSize()));
            }
            //设置字体加粗
            if (null != excelSheet.getFontWeight()) {
                if ("1".equals(excelSheet.getFontWeight())) {
                    font2.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体显示
                } else {
                    font2.setBoldweight(Font.BOLDWEIGHT_NORMAL);//粗体显示
                }
            }
            //字体设置
            styleTitle.setFont(font2);
            if (null != excelSheet.getHeadBackgroundColor()) {
                styleTitle.setFillForegroundColor((short) Integer.parseInt(excelSheet.getHeadBackgroundColor()));
                styleTitle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            }
        }
        cell.setCellValue(col.getTitle());
        cell.setCellStyle(styleTitle);
        return cellNum;
    }

    /**
     * 
     * @Title: generateBody
     * @Description: 生成表体数据
     * @param element
     *            设定文件
     * @return void 返回类型
     * @throws
     */
    private void generateBody(Object element) {
        if (element == null) {
            return;
        }

        Row row = hSheet.createRow(hSheet.getLastRowNum() + 1);
        int j = 0;
        JXPathContext jxpathContext = JXPathContext.newContext(element);
        for (int i = 0; i < excelSheet.getColumns().size(); i++) {
            ExcelColumn column = excelSheet.getColumns().get(i);
            if (null == column.getRowspan() && null == column.getClospan()) {
                Object value = jxpathContext.getValue(column.getProperty());
                // 取出当前应该写入的值
                String frmValue = StringFormat.toString(column.getDisplayPattern(), value);

                // 设置当前单元格格式
                Cell cell = row.createCell(j++);
                cell.setCellValue(frmValue);
                cell.setCellStyle(styleBorder);
                // 合计处理
                if (column.isSummary()) {
                    sumCols.put(i, (sumCols.get(i) == null ? 0 : sumCols.get(i)) + (new Double(value.toString())));
                }
            }
        }
    }

    /**
     * 
     * @Title: generateSum
     * @Description: 处理合计
     * @param 设定文件
     * @return void 返回类型
     * @throws
     */
    private void generateSum() {
        if (!sumCols.isEmpty()) {
            Row row = hSheet.createRow(hSheet.getLastRowNum() + 1);
            for (Entry<Integer, Double> sumCol : sumCols.entrySet()) {
                Cell cell = row.createCell(sumCol.getKey());
                cell.setCellValue(sumCol.getValue());
            }
        }
    }

    /**
     * 
     * @Title: writeToExcel
     * @Description: 生成excel文件
     * @param data
     * @param outputStream
     *            设定文件
     * @return void 返回类型
     * @throws
     */
    @SuppressWarnings("unchecked")
    public void writeToExcel(List<?> data, OutputStream outputStream) {
        try {

            init(); // 初始化

            generateTitle(); // 生成文件标题和表单标题

            for (Object element : data) { // 循环写记录
                if (convertor == null) {
                    generateBody(element);
                } else {
                    generateBody(convertor.convert(element));
                }

                cnt++; // 计数分sheet用

                if (cnt % MAX_PER_SHEET == 0) {
                    hSheet = workbook.createSheet("sheet" + (sheetNum++));
                    generateTitle();
                }
            }

            // 写入合计项
            generateSum();

            // 输出excel内容到文件流
            workbook.write(outputStream);
        } catch (IOException e) {
            logger.error("write excel error", e);
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.flush();
                    outputStream.close();
                }
                clean();
            } catch (IOException e) {
                logger.error("close stream error", e);
            }
        }
    }

    /**
     * 
     * @Title: clean
     * @Description: 清理
     * @return void 返回类型
     * @throws
     */
    private void clean() {
        this.cnt = 0;
        this.workbook = null;
        this.sheetNum = 0;
    }

    public void setConvertor(@SuppressWarnings("rawtypes") ObjectConvertor convertor) {
        this.convertor = convertor;
    }

    public void setVxlsFile(String vxlsFile) {
        this.vxlsFile = vxlsFile;
    }

    public void setXlsVersion(String xlsVersion) {
        this.xlsVersion = xlsVersion;
    }

    public static void main(String[] args) {
        ExcelWriter cpsExcelWrite = new ExcelWriter();
        List<Map<String,Object>> list=new ArrayList();

        for(int i=0;i<=23;i++){
            Map<String,Object> map=new HashMap();
            String day = DateUtil.formatDateTime("yyyy-MM-dd", new Date());
            if(i<=9){
                day = day + " 0" + i;
                map.put("column2","汉字");
            }else{
                day=day+" "+i;
                map.put("column2",new BigDecimal("41912790.62"));
            }
            map.put("column1",day);
            map.put("column3",new BigDecimal("575777611.9"));
            map.put("column4",12345);
            map.put("column5",new BigDecimal("575777611.90"));
            map.put("column6",0);

            list.add(map);
        }
        cpsExcelWrite.setVxlsFile("monitor-compare-woorkbook.vxls");
        cpsExcelWrite.setXlsVersion("2007");
        try {
            cpsExcelWrite.writeToExcel(list, new FileOutputStream(new File("C:\\Users\\Public\\Pictures\\Sample Pictures\\fjsldf.xlsx")));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

}
